///2018-1-10 add function update some record.


#ifndef W_DB_H
#define W_DB_H

#include "/usr/include/mysql/mysql.h"
#include <cstdio>
#include <iostream>
#include <string>
#include <vector>
#include <cmath>
#include <algorithm>
#include <sys/stat.h>
#include <sys/types.h>
#include <unistd.h>

using namespace std ;
using std::cout;

///2018-1-10
struct WDictionary
{
	string key ;
	string value ;
	inline WDictionary(string k,string v):key(k),value(v){};
	WDictionary(string k,int v) ;
	WDictionary(string k,float v) ;
	WDictionary(string k,double v) ;
	inline WDictionary(){} ;
} ;

WDictionary::WDictionary(string k,int v) 
{
	char buff[32] ;
	sprintf(buff , "%d" , v) ;
	this->key = k ;
	this->value = string(buff) ;
}
WDictionary::WDictionary(string k,float v) 
{
	char buff[32] ;
	sprintf(buff , "%f" , v) ;
	this->key = k ;
	this->value = string(buff) ;
}
WDictionary::WDictionary(string k,double v) 
{
	char buff[32] ;
	sprintf(buff , "%f" , v) ;
	this->key = k ;
	this->value = string(buff) ;
}
///2018-1-10 end 


//获取当前日期，格式YYYYMMDD HH:mm:ss 2017-11-14
std::string current_datetimestr()
{
  time_t theTime = time(NULL);
  struct tm *aTime = localtime(&theTime);
  int day = aTime->tm_mday;
  int month = aTime->tm_mon + 1;
  int year = aTime->tm_year + 1900;

  int hour = aTime->tm_hour;
  int minu = aTime->tm_min;
  int sec = aTime->tm_sec;

  char buff[30];
  sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d", year, month, day , hour ,minu , sec );
  return std::string(buff);
}





class wDb
{
public:

	inline wDb():conn(0){} ;

	bool connect( string host , 
		string user , 
		string pwd , 
		string db , string& error ) ;

	void insertimg(string tb,string pid,string fpath,string fname,string thumb,int ymd) ;
	void insertimg(string tb,string pid,string fpath,string fname,string thumb,string ymd8str) ;

	void insertxl(string tb,string pid,string maskid,
		string value,string ymd8str);
	void insertxl(string tb,string pid,int maskid,
		double value,int ymd);
	void insertUpdateXl(string tb,string pid,string maskid, string value,string ymd8str) ;
	void insertUpdateImg(string tb,string pid,string fpath,string fname,string thumb,string ymd8str) ;

	bool hasimg( string pid , int ymd ) ;
	bool hasxl(string pid , string maskid , int ymd ) ;
	bool hasxl2(string pid , string maskid , int ymd , string& rid) ;
    
    //获取序列产品pid在指定时间ymd下的全部maskid和主键pkarr
    bool getxlmaskidandridarray(string pid , int ymd , vector<string>& maskidarr ,  vector<string>& pkarr ) ;
    //检查该记录是否已有，如果没有生成插入sqlstr，否则生成update sqlstr，只生成sql，不进行插入。
    string insertUpdateXlSqlStr(string tb,string pid,string maskid, string value,string ymd8str,vector<string>& maskidarr,vector<string>& pkarr) ;
    //全部sql一起提交,返回影像的行数
    int submitManySql(string sqlstr) ;
    
    
	bool hasimg2( string pid , int ymd , string& rid ) ;

	bool updateSome( string tb , string pid , std::vector<WDictionary>& dictvec) ;

	bool haslostdatalog( string pid , string bzymd , string state ) ;
	void loglostdata( string program , string config , string pid , string bzcmd, string bzymd , string content , string state ) ;

	inline ~wDb(){ mysql_close(conn); conn=0; } ;

	string ymd8toy_m_d(string ymd8) ;

	string ymditoy_m_d(int ymdi) ;

private:
	MYSQL* conn ;


} ;

string wDb::ymd8toy_m_d(string ymd8) 
{
	string y = ymd8.substr(0,4) ;
	string m = ymd8.substr(4,2) ;
	string d = ymd8.substr(6,2) ;
	return y+ "-" + m + "-" + d ;
}
//
bool wDb::connect(string host , 
		string user , 
		string pwd , 
		string db, string& error ) 
{
	if( conn == 0 )
	{
		conn = mysql_init(0) ;
	    if( mysql_real_connect(conn , 
	                            host.c_str() , 
	                            user.c_str() , 
	                            pwd.c_str() , 
	                            db.c_str() , 
	                            0 , 0 , CLIENT_MULTI_STATEMENTS )
	                    == NULL )
	    { 
	    	cout<<"Error: mysql failed to connect."<<endl ;
            error = mysql_error(conn) ;
            return false ;
	    }else{
            cout<<"connect mysql success."<<endl ;
            return true ;
        }
	}
    else{
        cout<<"all ready connected. "<<endl ;
        return true ;//already connected
    }
}



void wDb::insertimg(string tb,string pid,string fpath,string fname,string thumb,int ymd) 
{
	char buff[16] ;
	sprintf(buff , "%d" , ymd ) ;
	string ymdstr(buff) ;
	this->insertimg(tb,pid,fpath,fname,thumb,ymdstr) ;
}
void wDb::insertimg(string tb,string pid,string fpath,string fname,string thumb,string ymd8str) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return ;
	}

	string ymdstr = this->ymd8toy_m_d(ymd8str) ;

	bool hasone = this->hasimg( pid , (int)atof(ymd8str.c_str()) ) ;
	
	if( hasone == false )
	{
        string sql = string("insert into ")+tb+" (product_id,data_filepath,filename,start_date,end_date,thumb,big_thumb) values ("  ;
        sql += pid + ","
                + "'" +  fpath  + "',"
                + "'" +  fname  + "',"
                + "'" +  ymdstr  + "',"
                + "'" +  ymdstr  + "',"
                + "'" +  thumb  + "',"
                + "'' " 
                + " );" ;
        mysql_query(conn , sql.c_str()) ;
        int id = mysql_insert_id(conn) ;
        if( id > 0 )
        {
        	cout<<"insert ok:"<<id<<" , "
			<<pid<<","<<fname<<","<<thumb<<","<<ymdstr<<endl;
        }else 
        {
        	cout<<"insert failed:"
			<<sql<<endl ; 
        }
        while( mysql_next_result(conn)==0 ){} ;
    }
    else 
	{
		cout<<"Already in db:"
			<<pid<<","<<fname<<","<<thumb<<","<<ymdstr<<endl;
	}
}


//2018-2-23
void wDb::insertUpdateImg(string tb,string pid,string fpath,string fname,string thumb,string ymd8str) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return ;
	}

	string ymdstr = this->ymd8toy_m_d(ymd8str) ;
	
	string rid = "0" ;
	bool hasone = this->hasimg2( pid , (int)atof(ymd8str.c_str()) , rid ) ;
	
	if( hasone == false )
	{
        string sql = string("insert into ")+tb+" (product_id,data_filepath,filename,start_date,end_date,thumb,big_thumb) values ("  ;
        sql += pid + ","
                + "'" +  fpath  + "',"
                + "'" +  fname  + "',"
                + "'" +  ymdstr  + "',"
                + "'" +  ymdstr  + "',"
                + "'" +  thumb  + "',"
                + "'' " 
                + " );" ;
        mysql_query(conn , sql.c_str()) ;
        int id = mysql_insert_id(conn) ;
        if( id > 0 )
        {
        	cout<<"insert ok:"<<id<<" , "
			<<pid<<","<<fname<<","<<thumb<<","<<ymdstr<<endl;
        }else 
        {
        	cout<<"insert failed:"
			<<sql<<endl ; 
        }
    }
    else 
	{
		cout<<"update data_id "<<rid<<":"<<endl;

		//修改这里 如果已有记录那么插入
		string sql = string("UPDATE ") + tb + " SET " 
			+ " data_filepath='"+fpath+"' , "
			+ " filename='" + fname + "' , "
			+ " thumb='" + thumb + "' "  ;
		sql += " WHERE data_id='" + rid + "' ;"  ;
		cout<<sql<<endl ;
		//UPDATE table_name SET col_name1="",col_name2=""  WHERE id=interid
		if( mysql_query(conn , sql.c_str() ) )
		{
			cout<<"Error : update query failed."<<endl ;
		} else
		{
			int naffected = mysql_affected_rows(conn) ;
			cout<<"updated rows "<<naffected<<endl ;
            while( mysql_next_result(conn)==0 ){} ;
		}
	}
}




void wDb::insertxl(string tb,string pid,int maskid,
	double value,int ymd)
{

	char buf[16] ;
	sprintf(buf , "%d" , maskid ) ;
	string maskidstr(buf) ;

	char buff[16] ;
	sprintf(buff , "%d" , ymd ) ;
	string ymdstr(buff) ;

	char buff2[20] ;
	sprintf(buff2 , "%lf" , value ) ;
	string valstr(buff2) ;

	this->insertxl(tb,pid,maskidstr,valstr,ymdstr) ;
}


void wDb::insertxl(string tb,string pid,string maskid,
	string value,string ymd8str)
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return ;
	}

	string ymdstr = this->ymd8toy_m_d(ymd8str) ;

	bool hasone = this->hasxl( pid , maskid , (int)atof(ymd8str.c_str()) ) ;
		
	if( hasone == false )
	{
        string sql = string("insert into ")+tb+" (product_id,mask,value,start_date) values ("  ;
        sql += pid + ","
                +  maskid  + ","
                +  value   + ","   
                + "'" + ymdstr + "'" 
                + " );" ;
        mysql_query(conn , sql.c_str()) ;
        int id = mysql_insert_id(conn) ;
        if( id > 0 )
        {
        	cout<<"insert ok:"<<id<<" , "
			<<pid<<","<<maskid<<","<<value<<","<<ymdstr<<endl;
        }else 
        {
        	cout<<"insert failed:"<<
			sql<<endl ;
        }
    }
    else 
	{
		cout<<"Already in db:"
			<<pid<<","<<maskid<<","<<value<<","<<ymdstr<<endl;
	}
}

void wDb::insertUpdateXl(string tb,string pid,string maskid,
	string value,string ymd8str)
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return ;
	}

	string ymdstr = this->ymd8toy_m_d(ymd8str) ;

	string rid = "0" ;
	bool hasone = this->hasxl2( pid , maskid , (int)atof(ymd8str.c_str()) , rid ) ;
		
	if( hasone == false )
	{
        string sql = string("insert into ")+tb+" (product_id,mask,value,start_date) values ("  ;
        sql += pid + ","
                +  maskid  + ","
                +  value   + ","   
                + "'" + ymdstr + "'" 
                + " );" ;
        cout<<sql<<endl ;
        mysql_query(conn , sql.c_str()) ;
        int id = mysql_insert_id(conn) ;
        if( id > 0 )
        {
        	cout<<"insert ok:"<<id<<" , "
			<<pid<<","<<maskid<<","<<value<<","<<ymdstr<<endl;
        }else 
        {
        	cout<<"insert failed:"<<
			sql<<endl ;
        }
    }
    else 
	{
		cout<<"update data_id "<<rid<<":"
			<<pid<<","<<maskid<<","<<value<<","<<ymdstr<<endl;

		//修改这里 如果已有记录那么插入
		string sql = string("UPDATE tb_xulie_data SET value='") + value + "' "  ;
		sql += " WHERE data_id='" + rid + "'; "  ;
		cout<<sql<<endl ;
		//UPDATE table_name SET col_name1="",col_name2=""  WHERE id=interid
		if( mysql_query(conn , sql.c_str() ) )
		{
			cout<<"Error : update query failed."<<endl ;
		} else
		{
			int naffected = mysql_affected_rows(conn) ;
			cout<<"updated rows "<<naffected<<endl ;
            while( mysql_next_result(conn)==0 ){} ;
		}
	}
}


string wDb::ymditoy_m_d(int ymdi) 
{
	int y = ymdi/10000 ;
	int m = (ymdi%10000)/100 ;
	int d = ymdi%100 ;
	char buff[16] ;
	sprintf(buff , "%04d-%02d-%02d" , y , m , d ) ;
	return string(buff) ;
}
bool wDb::hasimg( string pid , int ymd ) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	string ymdstr = this->ymditoy_m_d(ymd) ;
	string sql = "SELECT count(*) FROM  tb_product_data "   ;
	sql += " WHERE " ;
	sql += " product_id='" + pid + "' " ;
	sql += " AND start_date='" + ymdstr + "';" ;//结尾不能有多余空格

	if( mysql_query(conn , sql.c_str() ) != 0 )
	{
		cout<<"Error : select count query failed.mysqlerror:"<< mysql_error(conn) <<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	MYSQL_ROW row = mysql_fetch_row(res) ;
	int count = atof(row[0]) ;
	mysql_free_result(res) ;
	res = NULL ;
    while( mysql_next_result(conn)==0 ){} ;
	if( count > 0 ) return true ;
	else return false ;
}

//2018-2-23
bool wDb::hasimg2( string pid , int ymd , string& rid ) 
{
	rid = "0" ;
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	string ymdstr = this->ymditoy_m_d(ymd) ;
	string sql = "SELECT data_id FROM  tb_product_data "   ;
	sql += " WHERE " ;
	sql += " product_id='" + pid + "' " ;
	sql += " AND start_date='" + ymdstr + "' LIMIT 1;" ;


	if( mysql_query(conn , sql.c_str() )!=0 )
	{
		cout<<"Error : select data_id query failed.mysqlerror:"<< mysql_error(conn) <<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	MYSQL_ROW row = mysql_fetch_row(res) ;
    
	if( row == NULL )
	{
        while( mysql_next_result(conn)==0 ){} ;
		return false ;
	}else
	{
		rid = string(row[0]) ;
		mysql_free_result(res) ;
		res = NULL ;
        while( mysql_next_result(conn)==0 ){} ;
		return true ; 
	}
}


bool wDb::hasxl(string pid , string maskid , int ymd ) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	string ymdstr = this->ymditoy_m_d(ymd) ;
	string sql = "SELECT count(*) FROM  tb_xulie_data "   ;
	sql += " WHERE " ;
	sql += " product_id='" + pid + "' " ;
	sql += " AND mask='" + maskid + "' " ;
	sql += " AND start_date='" + ymdstr + "' ;" ;

	if( mysql_query(conn , sql.c_str() ) !=0 )
	{
		cout<<"Error : select count query failed."<<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	MYSQL_ROW row = mysql_fetch_row(res) ;
	int count = atof(row[0]) ;
	mysql_free_result(res) ;
	res = NULL ;
    while( mysql_next_result(conn)==0 ){} ;
	if( count > 0 ) return true ;
	else return false ;
}

//2018-2-23
bool wDb::hasxl2(string pid , string maskid , int ymd , string& rid) 
{
	rid = "0" ;
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	string ymdstr = this->ymditoy_m_d(ymd) ;
	string sql = "SELECT data_id FROM  tb_xulie_data "   ;
	sql += " WHERE " ;
	sql += " product_id='" + pid + "' " ;
	sql += " AND mask='" + maskid + "' " ;
	sql += " AND start_date='" + ymdstr + "' LIMIT 1 ;" ;

	if( mysql_query(conn , sql.c_str() ) !=0)
	{
		cout<<"Error : select data_id query failed."<<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	MYSQL_ROW row = mysql_fetch_row(res) ;
	if( row == NULL )
	{
        while( mysql_next_result(conn)==0 ){} ;
		return false ;
	}else
	{
		rid = string(row[0]) ;
		mysql_free_result(res) ;
		res = NULL ;
        while( mysql_next_result(conn)==0 ){} ;
		return true ; 
	}
}

//2021-3-13
bool wDb::getxlmaskidandridarray(string pid , int ymd , vector<string>& maskidarr ,  vector<string>& pkarr ) 
{
    maskidarr.clear() ;
    pkarr.clear() ;
    
    if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	string ymdstr = this->ymditoy_m_d(ymd) ;
	string sql = "SELECT data_id,mask FROM  tb_xulie_data "   ;
	sql += " WHERE " ;
	sql += " product_id='" + pid + "' " ;
	sql += " AND start_date='" + ymdstr + "' ;" ;

	if( mysql_query(conn , sql.c_str() )!=0 )
	{
		cout<<"Error : select data_id,mask query failed.mysqlerror:"<< mysql_error(conn) <<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	{
        string mid,pkid;
        MYSQL_ROW row;
        while( (row = mysql_fetch_row(res) ) )
        {
            pkid = string(row[0]) ;
            mid = string(row[1]) ;
            pkarr.push_back(pkid) ;
            maskidarr.push_back(mid) ;
        } 
		mysql_free_result(res) ;
		res = NULL ;
        while( mysql_next_result(conn)==0 ){} ;
		return true ; 
	}
}

//2021-3-13
string wDb::insertUpdateXlSqlStr(string tb,string pid,
    string maskid, string value,
    string ymd8str,vector<string>& maskidarr,vector<string>& pkarr) 
{
    int findindex = -1 ;
    for(int im = 0 ; im < maskidarr.size() ; ++ im ){
        if( maskidarr[im] == maskid ){
            findindex = im ;
            break ;
        }
    }
    if( findindex >= 0 ){
        //update
        string rid = pkarr[findindex] ;
        string sql = string("UPDATE tb_xulie_data SET value='") + value + "' "  ;
        sql += " WHERE data_id='" + rid + "' ;"  ;
        return sql ;
    }else{
        //insert
        string ymdstr = this->ymd8toy_m_d(ymd8str) ;
        string sql = string("insert into ")+tb+" (product_id,mask,value,start_date) values ("  ;
        sql += pid + ","
                +  maskid  + ","
                +  value   + ","   
                + "'" + ymdstr + "'" 
                + " );" ;
        return sql ;
    }    
}
//2021-3-13
//全部sql一起提交,返回影像的行数
int wDb::submitManySql(string sqlstr) 
{
    if( mysql_query(conn , sqlstr.c_str() )!=0 )
    {
        cout<<"Error : update query failed. mysqlerror:"<< mysql_error(conn) <<endl ;
        return -1 ;
    }else
    {
        int naffected = mysql_affected_rows(conn) ;
        cout<<"updated rows "<<naffected<<endl ;
        while( mysql_next_result(conn)==0 ){} ;
        return naffected ;
    }
}

//2018-1-10
bool wDb::updateSome( string tb , string pid , std::vector<WDictionary>& dictvec) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}
	if( dictvec.size() == 0  )
	{
		cout<<"Error : empty dictionary vector."<<endl ;
		return false;
	}
	if( pid == ""  )
	{
		cout<<"Error : primary key is empty."<<endl ;
		return false;
	}
	if( tb == ""  )
	{
		cout<<"Error : tablename is empty."<<endl ;
		return false;
	}

	string sql = "UPDATE "+tb+" SET "   ;
	for(int ic = 0 ; ic < dictvec.size() ; ++ ic )
	{
		sql += dictvec[ic].key + "='" + dictvec[ic].value + "'" ;
		if( ic != dictvec.size()-1 )
		{
			sql += "," ;
		}
	}
	sql += " WHERE id="+pid + ";";
	//UPDATE table_name SET col_name1="",col_name2=""  WHERE id=interid
	if( mysql_query(conn , sql.c_str() ) )
	{
		cout<<"Error : update query failed."<<endl ;
	    return false;
	} 
	int naffected = mysql_affected_rows(conn) ;
	cout<<"updated rows "<<naffected<<endl ;
    while( mysql_next_result(conn)==0 ){} ;
	return true ;
}



bool wDb::haslostdatalog( string pid , string bzymd , string state ) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return false;
	}

	string sql = "SELECT logid FROM  tb_product_log "   ;
	sql += " WHERE " ;
	sql += " bzymd='" + bzymd + "' " ;
	sql += " AND pid='" +  pid + "' " ;
	sql += " AND state='" + state + "' LIMIT 1;" ;

	if( mysql_query(conn , sql.c_str() ) )
	{
		cout<<"Error : select count query failed."<<endl ;
	    return false;
	} 
	MYSQL_RES * res = mysql_store_result(conn) ;
	MYSQL_ROW row = mysql_fetch_row(res) ;
	mysql_free_result(res) ;
    while( mysql_next_result(conn)==0 ){} ;
	if( row == NULL )
	{
		return false ;
	}else
	{
		return true ;
	}
}
void wDb::loglostdata( string program , string config , string pid , string bzcmd, string bzymd , string content , string state ) 
{
	if( conn == 0 )
	{
		cout<<"Error : no connection."<<endl ;
		return ;
	}

	if( this->haslostdatalog( pid , bzymd , state )  )
	{
		return ;
	}

	string dtstr = current_datetimestr() ;

    string sql = string("insert into tb_product_log (program,config,pid,bzcmd,bzymd,log_time,content,state) values (")  ;
    sql += "'" + program + "',"
    		+ "'" + config + "',"
    		+ "'" + pid + "',"
    		+ "'" + bzcmd + "',"
    		+ "'" + bzymd + "',"
    		+ "'" + dtstr + "',"
    		+ "'" + content + "',"
    		+ "'" + state + "'"
            + " );" ;
    mysql_query(conn , sql.c_str()) ;
    int id = mysql_insert_id(conn) ;
    if( id > 0 )
    {
    	std::cout<<"insert ok:"<<id<<std::endl;
    }else 
    {
    	std::cout<<"insert failed:"<<sql<<std::endl ;
    }
   
}

#endif